'''
数据库访问模块
'''
import sqlalchemy
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import os
import fcntl
import functools
from model.models import Base, modelDeletedcategory, modelWordCount
from log import log
from utils import dbfilelock
import settings

datastorelog = log(filename=settings.DB_LOG_FILE, level=log.INFO)

class datastore():

    def __init__(self):
        '''
        连接sqlite3数据库，并且尝试创建所有表
        '''
        self.engine = create_engine(settings.DB_FILE_STR, encoding='utf-8')
        self.DBSession = sessionmaker(bind=self.engine)
        self.logging = datastorelog
        try:
            Base.metadata.create_all(self.engine)
        except Exception as e:
            self.logging.info(e)

    @dbfilelock
    def batchAdd(self, obj):
        '''
        批量添加数据库
        '''
        ret = True
        try:
            session = self.DBSession()            
            for item in obj:
                self.logging.info('add item {}'.format(item))                
                session.add(item)
            session.commit()
        except sqlalchemy.exc.IntegrityError as e:
            self.logging.info (e)
            ret = False
        except Exception as e:
            self.logging.error(e)
            ret = False
        finally:            
            session.close()
        return ret

    @dbfilelock
    def add(self, obj):
        '''
        保存obj到数据库
        '''
        try:
            session = self.DBSession()
            session.add(obj)
            session.commit()
            self.logging.info('add item {}'.format(obj))
        except sqlalchemy.exc.IntegrityError as e:
            self.logging.info (e)
        except Exception as e:
            self.logging.error(e)
        finally:
            session.close()
    @dbfilelock
    def delete(self, obj):
        '''
        从数据库删除obj
        '''
        try:
            session = self.DBSession()
            session.delete(obj)
            session.commit()
            self.logging.info('del item {}'.format(obj))
        except sqlalchemy.exc.IntegrityError as e:
            self.logging.info (e)
        except Exception as e:
            self.logging.error(e)
        finally:
            session.close()
    @dbfilelock
    def all(self, table):
        '''
        返回数据库表的所有元素
        '''
        try:
            session = self.DBSession()
            result = session.query(table).all()
        except Exception as e:
            self.logging.error('all {}'.format(e))
        finally:
            session.close()

        return result
    
    @dbfilelock
    def count(self, table):
        '''
        返回数据库表内元素的个数
        '''
        try:
            session = self.DBSession()
            result = session.query(table).count()
        except Exception as e:
            self.logging.error('count {}'.format(e))
        finally:
            session.close()
            
        return result        
        
    @dbfilelock
    def top(self, table):
        '''
        返回数据库表的第一行元素,并且删除该元素
        '''
        try:
            session = self.DBSession()
            with session.no_autoflush:
                result = session.query(table).with_for_update().first()
                if result:
                    session.delete(result)
                session.commit()
        finally:
            session.close()

        return result
        
    @dbfilelock
    def queryDeletedCategory(self, link):        
        '''
        查询是否该目录链接是否已经分析过
        '''
        try:            
            session = self.DBSession()
            result = session.query(modelDeletedcategory).filter(modelDeletedcategory.link==link).count()
        except Exception as e:
            self.logging.error('queryDeletedCategory {}'.format(e))
        finally:
            session.close()
            return True if result != 0 else False
    @dbfilelock
    def updateWordCount(self, wordTuples):
        '''
        更新词频统计
        '''
        try:
            session = self.DBSession()
            for word, cnt in wordTuples:
                exists = session.query(modelWordCount).filter(modelWordCount.word == word).\
                        update({modelWordCount.count : modelWordCount.count + cnt}, synchronize_session=False)
                if exists == 0:
                    session.add(modelWordCount(word=word, count=cnt))
                    self.logging.info('add word : {}, count : {}'.format(word, cnt))
                else:                    
                    self.logging.info('update word : {}, count : {}'.format(word, cnt))
            session.commit()
            
        except Exception as e:
            self.logging.error('updateWordCount {}'.format(e))
        finally:
            session.close()

    @dbfilelock
    def topUntranslateWord(self) -> str:
        '''
        返回数据库第一个没有翻译的单词
        '''
        try:
            session = self.DBSession()
            word = None
            wordObj = session.query(modelWordCount).filter(modelWordCount.trans == None).order_by(modelWordCount.count.desc()).first()
            if wordObj:
                wordObj.trans = 'N/A'
                word = wordObj.word
            session.commit()
            
            return word
        except Exception as e:
            self.logging.error('topUntranslateWord {}'.format(e))
        finally:
            session.close()

    @dbfilelock
    def updateWordTrans(self, word, trans):
        '''
        更新单词翻译
        '''
        try:
            session = self.DBSession()
            lines = session.query(modelWordCount).filter(modelWordCount.word == word).\
                        update({modelWordCount.trans : trans}, synchronize_session=False)
            self.logging.info('updateWordTrans {}:{},changeLine={}'.format(word, trans, lines))
            session.commit()           
            
        except Exception as e:
            self.logging.error('updateWordTrans {}'.format(e))
        finally:
            session.close()

    @dbfilelock
    def getTopNWords(self, topN:int) -> [modelWordCount]:
        '''
        获取词频最大的topN个单词
        '''
        try:            
            session = self.DBSession()
            self.logging.info('getTopNWords topN={}'.format(topN))
            words = session.query(modelWordCount).filter(modelWordCount.trans!='N/A').order_by(modelWordCount.count.desc()).limit(topN).all()
        except Exception as e:
            self.logging.error('updateWordTrans {}'.format(e))
        finally:
            session.close()
        return words

def main():
    pass  

if __name__ == '__main__':
    main()